REM  SQL scripts for Chapter 6 of ORACLE8 DBA Handbook by Kevin Loney.
REM  See www.osborne.com for updates to these scripts.


Rem  create the base tables

drop table dbs;

rem * This table will store information about instances*/

create table DBS
(Db_Nm        VARCHAR2(8),     /*instance name*/
 Host_Nm      VARCHAR2(8),     /*host (server) name*/
 Description  VARCHAR2(80))    /*instance description*/
tablespace CC;

drop table FILES;

rem /*This table will store information about datafiles*/

create table FILES
(Db_Nm        VARCHAR2(8),     /*instance name*/
TS            VARCHAR2(30),    /*tablespace name*/
Check_Date    DATE,            /*date entry was made */
File_Nm       VARCHAR2(80),    /*file name*/
Blocks        NUMBER,          /*size of the file*/
primary key(Db_Nm, TS, Check_Date,File_Nm))
tablespace CC;

drop view FILES_TS_VIEW;

rem /*This view groups the file sizes by tablespace*/

create view FILES_TS_VIEW as
select
   Db_Nm,                      /*instance name*/
   TS,                         /*tablespace name*/
   Check_Date,                 /*date entry was made */
   SUM(Blocks) Sum_File_Blocks /*blocks allocated for ts*/
from FILES
group by
   Db_Nm,
   TS,
   Check_Date;

drop table SPACES;

rem /*This table will store information about free space*/

create table SPACES
(Db_Nm        VARCHAR2(8),   /*instance name*/
TS            VARCHAR2(30),  /*tablespace name*/
Check_Date    DATE,          /*date entry was made */
Count_Free_Blocks NUMBER,    /*number of free extents*/
Sum_Free_Blocks   NUMBER,    /*free space, in Ora blocks*/
Max_Free_Blocks   NUMBER,    /*largest free extent */ 
primary key (Db_Nm, Ts, Check_Date))
tablespace CC;

drop table EXTENTS;

rem /*This table will store information about extents */

create table EXTENTS
(Db_Nm   VARCHAR2(8),      /*instance name*/
TS       VARCHAR2(30),     /*tablespace name*/
Seg_Owner    VARCHAR2(30), /*segment owner*/
Seg_Name VARCHAR2(32),     /*segment name*/
Seg_Type VARCHAR2(17),     /*segment type*/
Extents  NUMBER,           /*number of extents allocated*/
Blocks   NUMBER,           /*number of blocks allocated*/
Check_Date    DATE,        /*date entry was made */
primary key (Db_Nm, TS, Seg_Owner, Seg_Name, Check_Date))
tablespace CC;


rem  create a database link

create database link CASE
connect to system identified by manager
using 'case';

rem  create a shell script to run the data insertion programs

# file:  ins_cc1
#
# This script is run once daily to insert records into the
# CC1 database recording the space usage of the databases
# listed in inserts.sql file called by this file.  New 
# databases need to have new links created in CC1 and have 
# to have entries in the # inserts.sql script. 
#
ORACLE_SID=cc1; export ORACLE_SID
ORAENV_ASK=NO; export ORAENV_ASK
. oraenv
cd /orasw/dba/CC1
svrmgrl <<EOF
connect internal
startup;
!sqlplus / @inserts
shutdown
EOF


rem  create a master SQL script for the insertions

rem
rem  file:  inserts.sql
rem  location:  /orasw/dba/CC1
rem  Called from ins_cc1 shell script.
rem  New entries must be made here every time a new database
rem  is added to the system.
rem
set verify off
@ins_all CASE
@ins_all CC1
analyze table FILES compute statistics;
analyze table SPACES compute statistics;
analyze table EXTENTS compute statistics;
analyze table RSEGS compute statistics;
@space_watcher
@extent_watcher


rem  Generate the inserts

rem
rem  file:  ins_all.sql
rem  location:  /orasw/dba/CC1
rem  Used to perform all inserts into CC1 monitoring 
rem  tables.  This script is called from inserts.sql for 
rem  each instance.
rem  For best results, name the database links after the
rem  instances they access.
rem
insert into FILES
   (Db_Nm,
   TS,
   Check_Date,
   File_Nm,
   Blocks)
select
   UPPER('&&1'),     /*insert database link,instance name*/
   Tablespace_Name,  /*tablespace name*/
   TRUNC(SysDate),   /*date query is being performed*/
   File_Name,        /*full name of database file*/
   Blocks            /*number of database blocks in file*/
from sys.DBA_DATA_FILES@&&1
/
commit;
rem
insert into SPACES
   (Db_Nm,
   Check_Date,
   TS,
   Count_Free_Blocks,
   Sum_Free_Blocks,
   Max_Free_Blocks)
select
   UPPER('&&1'),     /*insert database link,instance name*/
   TRUNC(SysDate),   /*date query is being performed*/
   Tablespace_Name,  /*tablespace name*/
   COUNT(Blocks),    /*num. of free space entries */
   SUM(Blocks),      /*total free space in the tablespace*/
   MAX(Blocks)       /*largest free extent in the ts*/
from sys.DBA_FREE_SPACE@&&1
group by Tablespace_Name
/
commit;
rem
insert into EXTENTS
   (Db_Nm,
   TS,
   Seg_Owner,
   Seg_Name,
   Seg_Type,
   Extents,
   Blocks,
   Check_Date)
select
   UPPER('&&1'),    /*insert database link,instance name*/
   Tablespace_Name, /*tablespace name*/
   Owner,           /*owner of the segment*/
   Segment_Name,    /*name of the segment*/
   Segment_Type,    /*type of segment (ex. TABLE, INDEX)*/
   Extents,         /*number of extents in the segment*/
   Blocks,         /*number of database blocks in segment*/
   TRUNC(SysDate)   /*date the query is being performed*/
from sys.DBA_SEGMENTS@&&1
where Extents>9          /*only record extended segments*/
or Segment_Type = 'ROLLBACK'   /*or rollback segments*/
/
commit;
rem
undefine 1


rem  generate the alert reports

rem
rem  file:  space_watcher.sql
rem  location:  /orasw/dba/CC1
rem  Called from inserts.sql
rem
rem  ...like some watcher of the skies
rem  when a new planet swims into his ken (Keats)
rem
column Db_Nm format A8
column TS format A20
column Week4 format 999 heading "1Wk|Ago"
column Week3 format 999 heading "2Wks|Ago"
column Week2 format 999 heading "3Wks|Ago"
column Week1 format 999 heading "4Wks|Ago"
column Today format 999
column Change format 999

set pagesize 60
break on Db_Nm skip 2
ttitle center 'Tablespaces whose PercentFree values have -
decreased 5 pct this month' skip 2

select
   SPACES.Db_Nm,
   SPACES.TS,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDateSysDate-28),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week1,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-21),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week2,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-14),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week3,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-7),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Week4,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Today,
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) Change
from SPACES, FILES_TS_VIEW FTV
where SPACES.Db_Nm = FTV.Db_Nm          /*same DB name*/
and SPACES.TS = FTV.TS                  /*same TS name*/
and SPACES.Check_Date = ftv.Check_Date  /*same check date*/
and exists                              /*does ts exist?*/
   (select 'x' from spaces x
   where x.db_nm = SPACES.db_nm
   and x.ts = SPACES.ts
   and x.Check_Date = TRUNC(SysDate))
group by
   SPACES.Db_Nm,
   SPACES.Ts
having               /*has percentfree changed 5 pct?*/
( ABS( MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
         ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
         ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)))
>5    )
or                  /*is percentfree less than 10?*/
( MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate),
      ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) <10)
order by SPACES.Db_Nm, 
   DECODE(MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)),0,9999,
   MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0)) -
   MAX(DECODE(SPACES.Check_Date, TRUNC(SysDate-28),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0))),
   MAX(DECODE(SPACES.Check_Date,TRUNC(SysDate),
     ROUND(100*Sum_Free_Blocks/Sum_File_Blocks),0))

spool space_watcher.lst
/
spool off


rem
rem  file:  ext_watcher.sql
rem  location:  /orasw/dba/CC1
rem  Called from inserts.sql
rem
rem  ...like some watcher of the skies
rem  when a new planet swims into his ken (Keats)
rem
column Db_Nm format A8
column TS format A18
column Seg_Owner format a14
column Seg_Name format a32
column Seg_Type format a8
column Blocks format 99999999
column Week4 format 999 heading "1Wk|Ago"
column Week3 format 999 heading "2Wks|Ago"
column Week2 format 999 heading "3Wks|Ago"
column Week1 format 999 heading "4Wks|Ago"
column Today format 999
column Change format 999

set pagesize 60 linesize 132
break on Db_Nm skip 2 on TS skip 1 on Seg_Owner
ttitle center 'Segments whose extent count is over 10' -
skip 2

select
   EXTENTS.Db_Nm,
   EXTENTS.TS,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Blocks,0)) Blocks,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)) Week1,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-21),
         Extents,0)) Week2,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-14),
         Extents,0)) Week3,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-7),
         Extents,0)) Week4,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0)) Today,
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)) Change
from EXTENTS
where exists  /*did this segment show up today?*/
   (select 'x' from EXTENTS x
   where x.Db_Nm = EXTENTS.Db_Nm
   and x.TX = EXTENTS.TX
   and x.Seg_Owner = EXTENTS.Seg_Owner
   and x.Seg_Name = EXTENTS.Seg_Name
   and x.Seg_Type = EXTENTS.Seg_Type
   and x.Check_Date = TRUNC(SysDate))
group by
   EXTENTS.Db_Nm,
   EXTENTS.TX,
   EXTENTS.Seg_Owner,
   EXTENTS.Seg_Name,
   EXTENTS.Seg_Type
order by EXTENTS.Db_Nm, EXTENTS.TS, 
   DECODE(MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0)),0,-9999,
   MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) -
   MAX(DECODE(EXTENTS.Check_Date, TRUNC(SysDate-28),
         Extents,0))) desc,
   MAX(DECODE(EXTENTS.Check_Date,TRUNC(SysDate),
         Extents,0)) desc

spool extent_watcher.lst
/
spool off



rem  Generate a space summary report

rem
rem space_summary.sql
rem  parameter 1: database link name
rem  parameter 2: check date
rem  parameter 3: ratio of Oracle to OS block size 
rem
rem  to call this report from within sqlplus:
rem  @space_summary link_name Check_Date block_ratio
rem
rem  Example:
rem  @space_summary CASE 07-NOV-98 4
rem
rem  Should be called weekly for each database.
rem
set pagesize 60 linesize 132 verify off feedback off 
set newpage 0
column TS heading 'Tablespace' format A18
column File_Nm heading 'File nm' format A40
column Blocks heading 'Orablocks'
column Percentfree format 999.99
column Diskblocks format 99999999
column Cfb format 9999999 heading 'NumFrExts'
column Mfb format 9999999 heading 'MaxFrExt'
column Sfb format 9999999 heading 'SumFrBl'
column Dfrb format 9999999 heading 'DiskFrBl'
column Sum_File_Blocks heading 'DiskBlocks'
column Maxfrpct heading 'MaxFrPct' format 9999999

break on TS
ttitle center 'Oracle Tablespaces in ' &&1 skip center -
'Check Date = ' &&2 skip 2 center
spool &&1._space_summary.lst

select
   Ts,                    /*tablespace name*/
   File_Nm,               /*file name*/
   Blocks,                /*Oracle blocks in the file*/
   Blocks*&&3 Diskblocks  /*OS blocks in the file*/
from FILES
where Check_Date = '&&2'
and Db_Nm = UPPER('&&1')
order by TS, File_Nm
/

ttitle center 'Oracle Free Space Statistics for ' &&1 -
skip center '(Extent Sizes in Oracle blocks)' skip center -
  'Check Date = ' &&2 skip 2

select
   SPACES.TS,                    /*tablespace name*/
   SPACES.Count_Free_Blocks Cfb, /*number of free extents*/
   SPACES.Max_Free_Blocks Mfb,   /*lgst free extent*/
   SPACES.Sum_Free_Blocks Sfb,   /*sum of free space*/
   ROUND(100*Sum_Free_Blocks/Sum_File_Blocks,2)
        Percentfree,             /*percent free in TS*/
   ROUND(100*Max_Free_Blocks/Sum_Free_Blocks,2)
    Maxfrpct,                /*ratio of largest extent to sum*/
   SPACES.Sum_Free_Blocks*&&3 Dfrb, /*disk blocks free*/
   Sum_File_Blocks*&&3 Sum_File_Blocks /*disk blocks allocated*/
from SPACES, FILES_TS_VIEW FTV
where SPACES.Db_Nm = FTV.Db_Nm
and SPACES.TS = FTV.TS
and SPACES.Check_Date = FTV.Check_Date
and SPACES.Db_Nm = UPPER('&&1')
and SPACES.Check_Date = '&&2'
/
spool off
undefine 1
undefine 2
undefine 3



rem  Purge the data

delete from FILES
 where Check_Date < SysDate-60;

commit;

delete from SPACES
 where Check_Date < SysDate-60;

commit;

delete from EXTENTS
 where Check_Date < SysDate-60;

commit;



create database link CASE_STAT
connect to sys identified by only_for_a_minute
using 'case';


Rem   modify utlbstat.sql

rem
rem  Modified version of $ORACLE_HOME/rdbms/admin/utlbstat.sql
rem  Note the addition of tablespace clauses and database links
rem
rem *************************************************************
rem                 First create all the tables
rem *************************************************************

drop table stats$begin_stats;
create table stats$begin_stats
TABLESPACE CC
as select * from v$sysstat@CASE_STAT where 0 = 1;
drop table stats$end_stats;
create table stats$end_stats
TABLESPACE CC
as select * from stats$begin_stats;

drop table stats$begin_latch;
create table stats$begin_latch
TABLESPACE CC
as select * from v$latch@CASE_STAT where 0 = 1;

drop table stats$end_latch;
create table stats$end_latch
TABLESPACE CC
as select * from stats$begin_latch;

drop table stats$begin_roll;
create table stats$begin_roll
TABLESPACE CC
as select * from v$rollstat@CASE_STAT where 0 = 1;

drop table stats$end_roll;
create table stats$end_roll
TABLESPACE CC
as select * from stats$begin_roll;

drop table stats$begin_lib;
create table stats$begin_lib
TABLESPACE CC
as select * from v$librarycache@CASE_STAT where 0 = 1;

drop table stats$end_lib;
create table stats$end_lib
TABLESPACE CC
as select * from stats$begin_lib;

drop table stats$begin_dc;
create table stats$begin_dc
TABLESPACE CC
as select * from v$rowcache@CASE_STAT where 0 = 1;

drop table stats$end_dc;
create table stats$end_dc
TABLESPACE CC
as select * from stats$begin_dc;

drop table stats$begin_event;
create table stats$begin_event
TABLESPACE CC
as select * from v$system_event@CASE_STAT where 0 = 1;

drop table stats$end_event;
create table stats$end_event
TABLESPACE CC
as select * from stats$begin_event;

drop table stats$begin_bck_event;
create table stats$begin_bck_event
  (event varchar2(200),
   total_waits number,
   time_waited number)
TABLESPACE CC;
drop table stats$end_bck_event;
create table stats$end_bck_event
as select * from stats$begin_bck_event;

drop table stats$dates;
create table stats$dates (stats_gather_times varchar2(100))
TABLESPACE CC;

drop view stats$file_view;
create view stats$file_view
as                 /*NOTE:  Have to change the FROM clause here*/
  select ts.name    ts,
         i.name     name,
         x.phyrds pyr,
         x.phywrts pyw,
         x.readtim prt,
         x.writetim pwt,
         x.phyblkrd pbr,
         x.phyblkwrt pbw,
         ROUND(i.bytes/1048576) megabytes_size
  from   v$filestat@CASE_STAT x,
         ts$@CASE_STAT ts,
         v$datafile@CASE_STAT i,
         file$@CASE_STAT f
 where i.file#=f.file#
   and ts.ts#=f.ts#
   and x.file#=f.file#;

drop table stats$begin_file;
create table stats$begin_file   /*No link needed here*/
TABLESPACE CC
as select * from stats$file_view where 0 = 1;

drop table stats$end_file;
create table stats$end_file
TABLESPACE CC
as select * from stats$begin_file;

drop table stats$begin_waitstat;
create table stats$begin_waitstat 
TABLESPACE CC
as select * from v$waitstat@CASE_STAT where 1=0;
drop table stats$end_waitstat;
create table stats$end_waitstat 
TABLESPACE CC
as select * from stats$begin_waitstat;


rem  a portion of utlestat:

insert into stats$end_latch select * from v$latch@CASE_STAT;
insert into stats$end_stats select * from v$sysstat@CAST_STAT;
insert into stats$end_lib select * from v$librarycache@CAST_STAT;
update stats$dates set end_time = SysDate;
insert into stats$end_event select * from v$system_event@CAST_STAT;
insert into stats$end_bck_event
  select event, sum(total_waits), sum(time_waited)
    from v$session@CAST_STAT s, v$session_event@CASE_STAT e
    where type = 'BACKGROUND' and s.sid = e.sid
    group by event;
insert into stats$end_waitstat select * from v$waitstat@CASE_STAT;
insert into stats$end_roll select * from v$rollstat@CAST_STAT;
insert into stats$end_file select * from stats$file_view; /*no link*/
insert into stats$end_dc select * from v$rowcache@CASE_STAT;


rem  The table creation portion of utlestat:


create table stats$stats
TABLESPACE CC
as select  e.value-b.value change , n.name
   from v$statname n ,  stats$begin_stats b , stats$end_stats e
   where n.statistic# = b.statistic# and n.statistic# = e.statistic#;

create table stats$latches
TABLESPACE CC
as select e.gets-b.gets gets,
   e.misses-b.misses misses,
   e.sleeps-b.sleeps sleeps,
   e.immediate_gets-b.immediate_gets immed_gets,
   e.immediate_misses-b.immediate_misses immed_miss,
   n.name
   from v$latchname n ,  stats$begin_latch b , stats$end_latch e
   where n.latch# = b.latch# and n.latch# = e.latch#;

create table stats$event
TABLESPACE CC
as select  e.total_waits-b.total_waits event_count,
          e.time_waited-b.time_waited time_waited,
          e.event
    from  stats$begin_event b , stats$end_event e
    where b.event = e.event
  union
  select  e.total_waits event_count,
          e.time_waited time_waited,
          e.event
    from  stats$end_event e
    where e.event not in (select b.event from stats$begin_event b);

create table stats$bck_event as
  select  e.total_waits-b.total_waits event_count,
          e.time_waited-b.time_waited time_waited,
          e.event
    from  stats$begin_bck_event b , stats$end_bck_event e
    where b.event = e.event
  union all
  select  e.total_waits event_count,
          e.time_waited time_waited,
          e.event
    from  stats$end_bck_event e
    where e.event not in (select b.event from stats$begin_bck_event b);

update stats$event e
  set (event_count, time_waited) = 
    (select e.event_count - b.event_count,
            e.time_waited - b.time_waited
      from stats$bck_event b
         where e.event = b.event)
   where e.event in (select b.event from stats$bck_event b);

create table stats$waitstat as
select  e.class,
        e.count - b.count count,
        e.time - b.time time
  from stats$begin_waitstat b, stats$end_waitstat e
   where e.class = b.class;

create table stats$roll
TABLESPACE CC
as select  e.usn undo_segment,
        e.gets-b.gets trans_tbl_gets,
   e.waits-b.waits trans_tbl_waits,
   e.writes-b.writes undo_bytes_written,
   e.rssize segment_size_bytes,
        e.xacts-b.xacts xacts,
   e.shrinks-b.shrinks shrinks,
        e.wraps-b.wraps wraps
   from stats$begin_roll b, stats$end_roll e
        where e.usn = b.usn;

create table stats$files
TABLESPACE CC
as select b.ts table_space,
       b.name file_name,
       e.pyr-b.pyr phys_reads,
       e.pbr-b.pbr phys_blks_rd,
       e.prt-b.prt phys_rd_time,
       e.pyw-b.pyw phys_writes,
       e.pbw-b.pbw phys_blks_wr,
       e.pwt-b.pwt phys_wrt_tim,
       e.megabytes_size
  from stats$begin_file b, stats$end_file e
       where b.name=e.name;

create table stats$dc
TABLESPACE CC
as select b.parameter name,
       e.gets-b.gets get_reqs,
       e.getmisses-b.getmisses get_miss,
       e.scans-b.scans scan_reqs,
       e.scanmisses-b.scanmisses scan_miss,
       e.modifications-b.modifications mod_reqs,
       e.count count,
       e.usage cur_usage
  from stats$begin_dc b, stats$end_dc e
       where b.cache#=e.cache#
        and  nvl(b.subordinate#,-1) = nvl(e.subordinate#,-1);

create table stats$lib
TABLESPACE CC
as select e.namespace,
       e.gets-b.gets gets,
       e.gethits-b.gethits gethits,
       e.pins-b.pins pins,
       e.pinhits-b.pinhits pinhits,
       e.reloads - b.reloads reloads,
       e.invalidations - b.invalidations invalidations
  from stats$begin_lib b, stats$end_lib e
       where b.namespace = e.namespace;



select Name from V$ROLLNAME
where USN = &UNDO_SEGMENT;


Rem   File I/O reports

clear columns
clear breaks
column Drive format A5
column File_Name format A30
column Blocks_Read format 99999999
column Blocks_Written format 99999999
column Total_IOs format 99999999
set linesize 80 pagesize 60 newpage 0 feedback off
ttitle skip center "Database File I/O Information" skip 2
break on report
compute sum of Blocks_Read on report
compute sum of Blocks_Written on report
compute sum of Total_IOs on report

select substr(DF.Name,1,5) Drive,
       SUM(FS.Phyblkrd+FS.Phyblkwrt) Total_IOs,
       SUM(FS.Phyblkrd) Blocks_Read,
       SUM(FS.Phyblkwrt) Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 group by substr(DF.Name,1,5)
 order by Total_IOs desc;


clear breaks
clear computes
break on Drive skip 1 on report
compute sum of Blocks_Read on Drive
compute sum of Blocks_Written on Drive
compute sum of Total_IOs on Drive
compute sum of Blocks_Read on Report
compute sum of Blocks_Written on Report
compute sum of Total_IOs on Report
ttitle skip center "Database File I/O by Drive" skip 2

select substr(DF.Name,1,5) Drive,
       DF.Name File_Name,
       FS.Phyblkrd+FS.Phyblkwrt Total_IOs,
       FS.Phyblkrd Blocks_Read,
       FS.Phyblkwrt Blocks_Written
  from V$FILESTAT FS, V$DATAFILE DF
 where DF.File#=FS.File#
 order by Drive, File_Name desc;


Rem   Over-extended segments

rem
rem  file:  over_extended.sql
rem  parameters:  database link name (instance name), multiplier
rem
rem  The "multiplier" value should always be greater than 1.
rem  Example:  To see segments that are within 20 per cent of their
rem      maximum extension, set the multiplier to 1.2.
rem
rem  Example call:
rem  @over_extended CASE 1.2
rem

select
   Owner,                  /*owner of segment*/
   Segment_Name,           /*name of segment*/
   Segment_Type,           /*type of segment*/
   Extents,                /*number of extents already acquired*/
   Blocks                  /*number of blocks already acquired*/
from DBA_SEGMENTS@&&1 s
where                      /*for cluster segments*/
(S.Segment_Type = 'CLUSTER' and exists
(select 'x' from DBA_CLUSTERS@&&1 c
where C.Owner = S.Owner
and C.Cluster_Name = S.Segment_Name
and C.Max_Extents <= S.Extents*&&2))
or                         /*for table segments*/
(s.segment_type = 'TABLE' and exists
(select 'x' from DBA_TABLES@&&1 t
where T.Owner = S.Owner
and T.Table_Name = S.Segment_Name
and T.Max_Extents <= S.Extents*&&2))
or                         /*for index segments*/
(S.Segment_Type = 'INDEX' and exists
(select 'x' from DBA_INDEXES@&&1 i
where I.Owner = S.Owner
and I.Index_Name = S.Segment_Name
and I.Max_Extents <= S.Extents*&&2))
or                         /*for rollback segments*/
(S.Segment_Type = 'ROLLBACK' and exists
(select 'x' from DBA_ROLLBACK_SEGS@&&1 r
where R.Owner = S.Owner
and R.Segment_Name = S.Segment_Name
and R.Max_Extents <= S.Extents*&&2))
order by 1,2

spool &&1._over_extended.lst
/
spool off
undefine 1
undefine 2



